package com.mage.dao.impl;

import com.mage.bean.Chongzhi;
import com.mage.bean.PageBean;
import com.mage.bean.User;
import com.mage.dao.ChongzhiDao;
import com.mage.dao.UserDao;
import com.mage.tool.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class ChongzhiDaoImpl implements ChongzhiDao {
    @Override
    public void insert(Chongzhi chongzhi) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        runner.update("insert into chongzhi ( `userid`, `username`, `money`, `czmethod`, `createtime`)  values(?,?,?,?,?)",
                chongzhi.getUserid(),
                chongzhi.getUsername(),
                chongzhi.getMoney(),
                chongzhi.getCzmethod(),
                chongzhi.getCreatetime()
        );
    }

    @Override
    public PageBean findChongzhiByPages(int currentPage) throws SQLException {
        //封装分页的该页数据
        PageBean<Chongzhi> pageBean = new PageBean<Chongzhi>();

        int pageSize = UserDao.PAGE_SIZE ;
        currentPage=currentPage<1?1:currentPage;
        pageBean.setCurrentPage(currentPage); //设置当前页
        pageBean.setPageSize(pageSize); //设置每页显示多少记录
        ChongzhiDao dao = new ChongzhiDaoImpl() ;
        List<Chongzhi> list =dao.findChongzhiByPage(currentPage);
        pageBean.setList(list); //设置这一页的会员数据
        //总的记录数， 总的页数。
        int count = dao.findCount();
        pageBean.setTotalSize(count); //设置总的记录数
        //200 ， 10 ==20   201 ， 10 = 21   201 % 10 == 0 ?201 / 10 :201 % 10 + 1
        pageBean.setTotalPage(count % pageSize==0 ? count / pageSize : (count / pageSize) + 1); //总页数
        return pageBean;
    }

    @Override
    public List<Chongzhi> findChongzhiByPage(int currentPage) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());

        return runner.query("select * from chongzhi limit ? offset ?", new BeanListHandler<Chongzhi>(Chongzhi.class), PAGE_SIZE , (currentPage-1)*PAGE_SIZE);
    }

    @Override
    public int findCount() throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        Long  result = (Long) runner.query("SELECT COUNT(*) FROM chongzhi " , new ScalarHandler() );
        return result.intValue();
    }

    /**
     * 统计
     * */
    @Override
    public PageBean todayStatistics(int currentPage) throws SQLException {
        //封装分页的该页数据
        PageBean<Chongzhi> pageBean = new PageBean<Chongzhi>();

        int pageSize = UserDao.PAGE_SIZE ;
        currentPage=currentPage<1?1:currentPage;
        pageBean.setCurrentPage(currentPage); //设置当前页
        pageBean.setPageSize(pageSize); //设置每页显示多少记录
        ChongzhiDao dao = new ChongzhiDaoImpl() ;
        List<Chongzhi> list =dao.todayStatisticsPages(currentPage);
        pageBean.setList(list); //设置这一页的会员数据

        //总的记录数， 总的页数。
        int count = dao.findCountByToday();
        pageBean.setTotalSize(count); //设置总的记录数
        //200 ， 10 ==20   201 ， 10 = 21   201 % 10 == 0 ?201 / 10 :201 % 10 + 1
        pageBean.setTotalPage(count % pageSize==0 ? count / pageSize : (count / pageSize) + 1); //总页数
        return pageBean;
    }
    @Override
    public List<Chongzhi> todayStatisticsPages(int currentPage) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        return runner.query("select * from chongzhi where TO_DAYS(createtime) = TO_DAYS(NOW()) limit ? offset ?", new BeanListHandler<Chongzhi>(Chongzhi.class), PAGE_SIZE , (currentPage-1)*PAGE_SIZE);
        //// offset 偏移前面的多少条，offset 1 跳过前面的一条
        // 第一个问号，一页返回多少条记录，第二个问号，跳过前面的多少条记录
        //5 0 --- 第一页 (1-1)*5
        //5 5  --- 第二页 (2-1)*5
        //5 10  --- 第三页
    }

    @Override
    public PageBean monthStatistics(int currentPage) throws SQLException {
        //封装分页的该页数据
        PageBean<Chongzhi> pageBean = new PageBean<Chongzhi>();

        int pageSize = UserDao.PAGE_SIZE ;
        currentPage=currentPage<1?1:currentPage;
        pageBean.setCurrentPage(currentPage); //设置当前页
        pageBean.setPageSize(pageSize); //设置每页显示多少记录
        ChongzhiDao dao = new ChongzhiDaoImpl() ;
        List<Chongzhi> list =dao.monthStatisticsPages(currentPage);
        pageBean.setList(list); //设置这一页的学生数据
        //总的记录数， 总的页数。
        int count = dao.findCountByMonth();
        pageBean.setTotalSize(count); //设置总的记录数
        //200 ， 10 ==20   201 ， 10 = 21   201 % 10 == 0 ?201 / 10 :201 % 10 + 1
        pageBean.setTotalPage(count % pageSize==0 ? count / pageSize : (count / pageSize) + 1); //总页数
        return pageBean;
    }

    @Override
    public List<Chongzhi> monthStatisticsPages(int currentPage) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        return runner.query("select * from chongzhi where DATE_FORMAT( createtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) limit ? offset ?", new BeanListHandler<Chongzhi>(Chongzhi.class), PAGE_SIZE , (currentPage-1)*PAGE_SIZE);
    }

    @Override
    public int findCountByToday() throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        Long  result = (Long) runner.query("SELECT COUNT(*) FROM chongzhi where TO_DAYS(createtime) = TO_DAYS(NOW()) " , new ScalarHandler() );
        return result.intValue();
    }

    @Override
    public int findCountByMonth() throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        Long  result = (Long) runner.query("SELECT COUNT(*) FROM chongzhi where DATE_FORMAT( createtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) " , new ScalarHandler() );
        return result.intValue();
    }

    @Override
    public PageBean findXiaofeiByPages(int currentPage, int userid) throws SQLException {
        //封装分页的该页数据
        PageBean<Chongzhi> pageBean = new PageBean<Chongzhi>();

        int pageSize = UserDao.PAGE_SIZE ;
        currentPage=currentPage<1?1:currentPage;
        pageBean.setCurrentPage(currentPage); //设置当前页
        pageBean.setPageSize(pageSize); //设置每页显示多少记录
        ChongzhiDao dao = new ChongzhiDaoImpl() ;
        List<Chongzhi> list =dao.findXiaofeiByPage(currentPage,userid);
        pageBean.setList(list); //设置这一页的学生数据
        //总的记录数， 总的页数。
        int count = dao.findXiaofeiCount(userid);
        pageBean.setTotalSize(count); //设置总的记录数
        //200 ， 10 ==20   201 ， 10 = 21   201 % 10 == 0 ?201 / 10 :201 % 10 + 1
        pageBean.setTotalPage(count % pageSize==0 ? count / pageSize : (count / pageSize) + 1); //总页数
        return pageBean;
    }

    @Override
    public List<Chongzhi> findXiaofeiByPage(int currentPage,int userid) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        return runner.query("select * from chongzhi where userid = ? limit ? offset ?", new BeanListHandler<Chongzhi>(Chongzhi.class), userid,PAGE_SIZE , (currentPage-1)*PAGE_SIZE);
    }
    @Override
    public int findXiaofeiCount(int userid) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        Long  result = (Long) runner.query("SELECT COUNT(*) FROM chongzhi where userid = ?" , new ScalarHandler(),userid );
        return result.intValue();
    }

    @Override
    public void delete(int id) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        runner.update("delete from chongzhi where id = ?",id);
        System.out.println("delete from chongzhi where id = "+id);
    }


}
